package com.idriver.dao;

import com.idriver.bean.Student;
import com.idriver.util.DBCPUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.List;

public class StudentDaoImpl implements StudentDao {

    /**
     * 获取学生列表
     * @param page 当前页
     * @param size  每页条数
     * @param keyword 搜索关键字, 根据学生姓名搜索
     * @return 返回学生记录列表
     */
    @Override
    public List<Student> getStudentLists(int page, int size, String keyword) {

        QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());

        String sql = "select * from student";
        if (null != keyword && keyword.length() > 0) {
            sql += " where name like ? ";
        }

        sql += " order by id desc limit ? offset ?";

        List<Student> students = null;

        try {
            Object[] params;
            if (null != keyword && keyword.length() > 0) {
                params = new Object[]{"%" + keyword + "%", size, (page - 1) * size};
            } else {
                params = new Object[]{size, (page - 1) * size};
            }


            students = qr.query(sql, new BeanListHandler<Student>(Student.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return students;
    }

    /**
     * 获取学生列表记录条数
     * @param keyword 搜索关键字, 根据学生姓名搜索
     * @return long
     */
    public int getStudentListNum(String keyword) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        int totalNum = 0;

        try {
            conn = DBCPUtil.getConnection();
            String sql = "select count(*) as total_num from student";
            if (null != keyword && keyword.length() > 0) {
                sql += " where name like ? ";
            }

            ps = conn.prepareStatement(sql);
            if (null != keyword && keyword.length() > 0) {
                ps.setString(1, "%" + keyword + "%");
            }

            resultSet = ps.executeQuery();
            totalNum = 0;
            if (resultSet.next()) {
                totalNum = resultSet.getInt("total_num");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBCPUtil.close(conn, ps, resultSet);
        }

        return totalNum;
    }

    /**
     * 新增一名学员
     * @param student
     * @return
     */
    @Override
    public int addStudent(Student student) {

        Connection conn = null;
        PreparedStatement ps = null;
        int affectedRow = 0;

        try {

            conn = DBCPUtil.getConnection();

            String sql = "insert into student values (null, ?, ?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql);

            ps.setString(1, student.getName());
            ps.setString(2, student.getSex());
            ps.setDate(3, student.getBirthday());
            ps.setString(4, student.getMail());
            ps.setString(5, student.getMark());
            ps.setTimestamp(6, student.getCreated());

            affectedRow = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBCPUtil.close(conn, ps);
        }

        return affectedRow;
    }

    @Override
    public int delStudent(int id) {

        Connection conn = null;
        PreparedStatement ps = null;
        int affectedRow = 0;

        try {
            conn = DBCPUtil.getConnection();

            String sql = "delete from student where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);

            affectedRow = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBCPUtil.close(conn, ps);
        }

        return affectedRow;
    }

    @Override
    public int editStudent(Student student) {

        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBCPUtil.getConnection();

            String sql = "update students set name = ?, sex = ?, birthday = ?, mail = ?, mark = ? , created = ? where id = ?";
            ps = conn.prepareStatement(sql);

            ps.setString(1, student.getName());
            ps.setString(2, student.getSex());
            ps.setDate(3, student.getBirthday());
            ps.setString(4, student.getMail());
            ps.setString(5, student.getMark());
            ps.setTimestamp(6, student.getCreated());
            ps.setInt(7, student.getId());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBCPUtil.close(conn, ps);
        }

        return 0;
    }

    @Override
    public Student findStudentById(int id) {

        Student student = null;
        try {
            QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());

            String sql = "select * from students where id = ?";
            student = qr.query(sql, new BeanHandler<>(Student.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return student;
    }
}
